package com.booza.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;

import com.booza.constants.DatabaseConstants;
import com.booza.constants.LeagueStatus;
import com.booza.constants.PlayerInvitationStatus;
import com.booza.model.League;
import com.booza.model.LeaguePlayers;

/**
 * A DAO level interface for league table.
 */
public class LeagueDAOImpl extends CommonDAOImpl implements LeagueDAO
{
	@Autowired
	private SessionFactory sessionFactory;
	
	public League findLeagueById(Integer id)
	{
		return (League) sessionFactory.getCurrentSession().get(League.class, id);
	}

	@SuppressWarnings("unchecked")
	public Collection<Integer> getLeaguesByTime(Date endDate)
	{
		Collection<League> l = (Collection<League>) sessionFactory.getCurrentSession().createCriteria(League.class).add(
				Restrictions.eq("endtime", endDate)).add(Restrictions.eq("status", 0)).list();
		if (l != null) {
			List<Integer> ids = new ArrayList<Integer>();
			for (League ll : l) {
				ids.add(ll.getLeagueid());
			}
			return ids;
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public Collection<LeaguePlayers> getConflictingLeagues(Date endDate, Integer accountId)
	{
		Collection<Integer> leagueIds = getLeaguesByTime(endDate);
		if (leagueIds == null || leagueIds.isEmpty()) {
			return null;
		}

		return (Collection<LeaguePlayers>) sessionFactory.getCurrentSession().createCriteria(LeaguePlayers.class).add(
				Restrictions.eq("accountid", accountId)).add(Restrictions.eq("accepted", 1)).add(
				Restrictions.in("leagueid", leagueIds)).list();
	}

	/**
	 * Find league by name.
	 */
	public League findLeagueByName(String name)
	{
		return (League) sessionFactory.getCurrentSession().createCriteria(League.class).add(Restrictions.eq("name", name))
				.uniqueResult();
	}

	public void createLeague(League league)
	{
		sessionFactory.getCurrentSession().save(league);
	}

	public void addPlayerToLeague(LeaguePlayers me)
	{
		sessionFactory.getCurrentSession().save(me);
	}

	/**
	 * Find all draft leagues by owner.
	 */
	@SuppressWarnings("unchecked")
	public Collection<League> findDraftLeagues(Integer accountId)
	{
		String query = "FROM League WHERE (((creator = :id) "
				+ ") AND (endtime > current_date OR endtime IS NULL) AND (status = " + LeagueStatus.DRAFT + ")"
				+ " ) ORDER BY endtime";
		return (Collection<League>) executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	public SessionFactory getSessionFactory()
	{
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory)
	{
		this.sessionFactory = sessionFactory;
	}

	/**
	 * Find all leagues player invited.
	 */
	@SuppressWarnings("unchecked")
	public Collection<League> findLeaguesInvitations(Integer accountId)
	{
		String query = "FROM League WHERE (leagueid IN (SELECT p.leagueid FROM LeaguePlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.NOT_RESPONDED
				+ ")) AND (endtime > now())";
		return (Collection<League>) executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find all passed leagues for the player.
	 */
	@SuppressWarnings("unchecked")
	public Collection<League> findPreviousLeagues(Integer accountId)
	{
		String query = "FROM League WHERE (((creator = :id) OR (leagueid IN (SELECT p.leagueid FROM LeaguePlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED + "))) AND (starttime < now()) AND (endtime < now())) ORDER BY endtime desc";
		return (Collection<League>) executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find all running leagues for the player.
	 */
	@SuppressWarnings("unchecked")
	public Collection<League> findRunningLeagues(Integer accountId)
	{
		String query = "FROM League WHERE (((creator = :id) OR (leagueid IN (SELECT p.leagueid FROM LeaguePlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED + "))) AND (starttime < now()) AND (endtime > now())) ORDER BY endtime";
		return (Collection<League>) executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find all upcoming leagues for the player.
	 */
	@SuppressWarnings("unchecked")
	public Collection<League> findUpcomingLeagues(Integer accountId)
	{
		String query = "FROM League WHERE (((creator = :id) OR (leagueid IN (SELECT p.leagueid FROM LeaguePlayers p WHERE p.accountid = :id AND p.accepted = "
				+ PlayerInvitationStatus.ACCEPTED
				+ "))) AND (starttime > now()) AND (endtime > now())) ORDER BY endtime";
		return (Collection<League>) executeQuery(query, DatabaseConstants.ID_COLUMN, accountId);
	}

	/**
	 * Find all league players (being invited).
	 */
	@SuppressWarnings("unchecked")
	public Collection<LeaguePlayers> getInvitedPlayers(Integer accountId, Integer leagueId)
	{
		return (Collection<LeaguePlayers>) sessionFactory.getCurrentSession().createCriteria(LeaguePlayers.class).add(
				Restrictions.eq("accountid", accountId)).add(Restrictions.eq("leagueid", leagueId)).list();
	}

	/**
	 * Find league player by ID.
	 */
	public LeaguePlayers findPlayersById(Integer accountId, Integer leagueId)
	{
		return (LeaguePlayers) sessionFactory.getCurrentSession().createCriteria(LeaguePlayers.class).add(
				Restrictions.eq("accountid", accountId)).add(Restrictions.eq("leagueid", leagueId)).uniqueResult();
	}

	/**
	 * Change league status. Only the owner can do it!
	 */
	public boolean changeLeagueStatus(Integer accountId, Integer leagueId, int status)
	{
		boolean changed = false;
		League league = (League) sessionFactory.getCurrentSession().get(League.class, leagueId);
		if (league != null) {
			// only owner can change the league status
			if (league.getCreator().equals(accountId) == true) {
				league.setStatus(status);
				sessionFactory.getCurrentSession().saveOrUpdate(league);
				changed = true;
			}
		}
		return changed;
	}

	/**
	 * Return table with players win count for league. 'Player name - win game count'.
	 */
	public List findLeagueWinsStats(Integer leagueId, List<Integer> playersIds)
	{
		// wins count
		String query = "SELECT p1,COUNT(P1) FROM (SELECT player1,player2,(CASE WHEN score1 > " +
				"score2 THEN player1 WHEN score1 < score2 THEN player2 END) AS p1 FROM stats " +
				"WHERE player1 IN (:players) AND player2 IN (:players) AND leagueid=:leagueid) AS A WHERE p1 IS NOT NULL GROUP BY 1";
		Query q = sessionFactory.getCurrentSession().createSQLQuery(query);
		q.setParameter("leagueid", leagueId);
		q.setParameterList("players", playersIds);
		return q.list();
	}

	/**
	 * Return table with players win count for league. 'Player name - lose game count'.
	 */
	public List findLeagueLoseStats(Integer leagueId, List<Integer> playersIds)
	{
		String query = "SELECT p1,COUNT(p1) FROM (SELECT player1, player2,(CASE WHEN score1 < " +
		"score2 THEN player1 WHEN score1 > score2 THEN player2 END) AS p1 FROM STATS " +
		"WHERE player1 IN (:players) AND player2 IN (:players) AND leagueid=:leagueid) AS a WHERE p1 IS NOT NULL GROUP BY 1";
		Query q = sessionFactory.getCurrentSession().createSQLQuery(query);
		q.setParameter("leagueid", leagueId);
		q.setParameterList("players", playersIds);
		return q.list();
	}

	/**
	 * Return table with players win count for league. 'Player name - lose game count'
	 */
	public List findLeagueDrawnStats(Integer leagueId, List<Integer> playersIds)
	{
		// lose count
		String query = "SELECT player1, player2, COUNT(wins) FROM " +
				"(SELECT player1, player2, (CASE WHEN score1 = score2 THEN player1 end) AS wins FROM stats WHERE player1" +
				" IN (:players) AND player2 IN (:players) AND leagueid=:leagueid)" +
				"AS a WHERE wins IS NOT NULL group BY 1";
		Query q = sessionFactory.getCurrentSession().createSQLQuery(query);
		q.setParameter("leagueid", leagueId);
		q.setParameterList("players", playersIds);
		return q.list();
	}

	/**
	 * Get all league stats for the player.
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> findLeagueGames(Integer leagueId, String player)
	{
		String query = "SELECT "
				+ "(SELECT userid FROM account WHERE accountid = player1) AS p1, "
				+ "(SELECT userid FROM account WHERE accountid = player2) AS p2, "
				+ " score1 AS score1, score2 AS score2 "
				+ " FROM stats WHERE leagueid=:leagueid AND (player1 ="
				+ " (SELECT accountid FROM account WHERE userid=:player)) OR (player2 = (SELECT accountid FROM account WHERE userid=:player))";
		Query q = sessionFactory.getCurrentSession().createSQLQuery(query);
		q.setParameter("leagueid", leagueId);
		q.setParameter("player", player);
		return q.list();
	}		

	@SuppressWarnings("unchecked")
	public List<League> findAllPreviousLeagues()
	{
		String query = "FROM League WHERE ((starttime < now()) AND (endtime < now()) "
				+ "AND (winStatus IS NOT NULL) AND (wager > 0) AND (paymentProcessed = 0) ) "
				+ "ORDER BY endtime desc";
		try {
			return sessionFactory.getCurrentSession().createQuery(query).list();
		}
		catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Find leagues matching the criteria.
	 * 
	 * @param colName
	 *          - colname of the criteria
	 * @param colValue
	 *          - keyword
	 * @return Return the matching tournaments.
	 */
	@SuppressWarnings("unchecked")
	public Collection<League> fetchAllByParam(String colName, Object colValue)
	{
		return (Collection<League>) sessionFactory.getCurrentSession().createCriteria(League.class).add(
				Restrictions.eq(colName, colValue)).list();
	}
}
